package com.huishu.tcm.controller;


import cn.dev33.satoken.stp.StpUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.huishu.tcm.entity.*;
import com.huishu.tcm.service.*;
import com.huishu.tcm.util.Constants;
import com.huishu.tcm.util.ObjUtil;
import com.huishu.tcm.util.RateLimit;
import com.huishu.tcm.vo.PersonalCenterVo;
import com.huishu.tcm.vo.PrescriptionVo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Api(tags = "糖尿病前期健康筛查、个性化管理方案、患者随访监测管理")
@RestController
@RequestMapping("/api/annex")
public class AnnexController {

    /**
     * 获取当前登录的用户
     * @return
     */
    public User getLoginUser(){
        return JSONObject.parseObject((String) StpUtil.getLoginId(),User.class);
    }

    @Resource
    Annex1Service annex1Service;
    @Resource
    Annex2Service annex2Service;
    @Resource
    Annex4Service annex4Service;
    @Resource
    Annex5Service annex5Service;
    @Resource
    Annex6Service annex6Service;
    @Resource
    CustomizeService customizeService;

    // 查询糖尿病前期健康筛查录入历史记录
    @ApiOperation(value = "查询糖尿病前期健康筛查历史记录",
        notes = "返回值：{annex1:[...],annex2:[...],...}")
    @GetMapping(value = "/queryHistoryAnnex", params = {"patientId"})
    public Map<String,Object> queryHistoryAnnex(int patientId){
        Map<String, Object> map = new HashMap<>();
        
        QueryWrapper<Annex1> annex1QueryWrapper = new QueryWrapper<>();
        annex1QueryWrapper.eq("patient_id", patientId);
        List<Annex1> annex1List = annex1Service.list(annex1QueryWrapper);
        map.put("annex1", annex1List);

        QueryWrapper<Annex2> annex2QueryWrapper = new QueryWrapper<>();
        annex2QueryWrapper.eq("patient_id", patientId);
        List<Annex2> annex2List = annex2Service.list(annex2QueryWrapper);
        map.put("annex2", annex2List);

        QueryWrapper<Annex4> annex4QueryWrapper = new QueryWrapper<>();
        annex4QueryWrapper.eq("patient_id", patientId);
        List<Annex4> annex4List = annex4Service.list(annex4QueryWrapper);
        map.put("annex4", annex4List);

        QueryWrapper<Annex5> annex5QueryWrapper = new QueryWrapper<>();
        annex5QueryWrapper.eq("patient_id", patientId);
        List<Annex5> annex5List = annex5Service.list(annex5QueryWrapper);
        map.put("annex5", annex5List);

        QueryWrapper<Annex6> annex6QueryWrapper = new QueryWrapper<>();
        annex6QueryWrapper.eq("patient_id", patientId);
        List<Annex6> annex6List = annex6Service.list(annex6QueryWrapper);
        map.put("annex6", annex6List);

        QueryWrapper<Customize> customizeQueryWrapper = new QueryWrapper<>();
        customizeQueryWrapper.eq("patient_id", patientId);
        List<Customize> customizeList = customizeService.list(customizeQueryWrapper);
        map.put("customize", customizeList);
        return map;
    }


    @Resource
    PrescriptionHealthEducationService prescriptionHealthEducationService; // 健康教育
    @Resource
    PrescriptionDietService prescriptionDietService;// 膳食
    @Resource
    PrescriptionMotionService prescriptionMotionService; // 运动
    @Resource
    PrescriptionLoseWeightService prescriptionLoseWeightService; // 减重
    @Resource
    PrescriptionSleepService prescriptionSleepService;// 睡眠
    @Resource
    PrescriptionPhysiqueService prescriptionPhysiqueService; // 偏颇体质调理
    @Resource
    PrescriptionOtherService prescriptionOtherService;// 其他处方

    // 查询个性化健康管理方案录入历史数据
    @ApiOperation(value = "查询个性化健康管理方案历史数据",
        notes = "返回值：{健康教育healthEducation:[...],膳食diet:[...]," +
            "运动motion:[...],减重loseWeight:[...],睡眠sleep:[...],偏颇体质调理physique:[...],其他other:[...]}")
    @GetMapping(value = "/queryHistoryPrescription", params = {"patientId"})
    public Map<String,Object> queryHistoryPrescription(int patientId){
        Map<String, Object> map = new HashMap<>();

        QueryWrapper<PrescriptionHealthEducation> healthEducationQueryWrapperQueryWrapper = new QueryWrapper<>();
        healthEducationQueryWrapperQueryWrapper.eq("patient_id", patientId);
        List<PrescriptionHealthEducation> healthEducationList = prescriptionHealthEducationService.list(healthEducationQueryWrapperQueryWrapper);
        map.put("healthEducation", healthEducationList);

        QueryWrapper<PrescriptionDiet> dietQueryWrapper = new QueryWrapper<>();
        dietQueryWrapper.eq("patient_id", patientId);
        List<PrescriptionDiet> dietList = prescriptionDietService.list(dietQueryWrapper);
        map.put("diet", dietList);

        QueryWrapper<PrescriptionMotion> motionQueryWrapper = new QueryWrapper<>();
        motionQueryWrapper.eq("patient_id", patientId);
        List<PrescriptionMotion> motionList = prescriptionMotionService.list(motionQueryWrapper);
        map.put("motion", motionList);

        QueryWrapper<PrescriptionLoseWeight> loseWeightQueryWrapper = new QueryWrapper<>();
        loseWeightQueryWrapper.eq("patient_id", patientId);
        List<PrescriptionLoseWeight> loseWeightList = prescriptionLoseWeightService.list(loseWeightQueryWrapper);
        map.put("loseWeight", loseWeightList);

        QueryWrapper<PrescriptionSleep> sleepQueryWrapper = new QueryWrapper<>();
        sleepQueryWrapper.eq("patient_id", patientId);
        List<PrescriptionSleep> sleepList = prescriptionSleepService.list(sleepQueryWrapper);
        map.put("sleep", sleepList);

        QueryWrapper<PrescriptionPhysique> physiqueQueryWrapper = new QueryWrapper<>();
        physiqueQueryWrapper.eq("patient_id", patientId);
        List<PrescriptionPhysique> physiqueList = prescriptionPhysiqueService.list(physiqueQueryWrapper);
        map.put("physique", physiqueList);

        QueryWrapper<PrescriptionOther> otherQueryWrapper = new QueryWrapper<>();
        otherQueryWrapper.eq("patient_id", patientId);
        List<PrescriptionOther> otherList = prescriptionOtherService.list(otherQueryWrapper);
        map.put("other", otherList);
        return map;
    }



    @Resource
    Annex7Service annex7Service;

    @ApiOperation(value = "查询中医健康管理随访监测记录表历史数据")
    @GetMapping(value = "/queryHistoryAnnex7", params = {"patientId"})
    public List<Annex7> queryHistoryAnnex7(int patientId){
        QueryWrapper<Annex7> annex7QueryWrapper = new QueryWrapper<>();
        annex7QueryWrapper.eq("patient_id", patientId);
        return annex7Service.list(annex7QueryWrapper);
    }


    @Resource
    UserService userService;

    @ApiOperation(value = "根据社区医疗机构id查询未完成的用户列表", notes = "根据社区医疗机构id查询未完成的用户列表")
    @GetMapping(value = "/queryToBeContinued", params = {"institutionId"})
    public List<User> queryToBeContinued(int institutionId){
        List<User> result = new ArrayList<>();
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.eq("institution_id", institutionId);
        userQueryWrapper.eq("role", Constants.USER_ROLE_PATIENT);
        userQueryWrapper.ne("incomplete_list_deleted",true);
        List<User> userList = userService.list(userQueryWrapper);
        for(User user: userList){
            // 已在未完成列表删除的用户不显示在未完成列表
            if(user.getIncompleteListDeleted()){
                continue;
            }
            QueryWrapper<Annex1> annex1QueryWrapper = new QueryWrapper<>();
            annex1QueryWrapper.eq("patient_id", user.getId());
            List<Annex1> annex1List = annex1Service.list(annex1QueryWrapper);
            
            QueryWrapper<Annex2> annex2QueryWrapper = new QueryWrapper<>();
            annex2QueryWrapper.eq("patient_id", user.getId());
            List<Annex2> annex2List = annex2Service.list(annex2QueryWrapper);
            
            QueryWrapper<Annex4> annex4QueryWrapper = new QueryWrapper<>();
            annex4QueryWrapper.eq("patient_id", user.getId());
            List<Annex4> annex4List = annex4Service.list(annex4QueryWrapper);
            
            QueryWrapper<Annex5> annex5QueryWrapper = new QueryWrapper<>();
            annex5QueryWrapper.eq("patient_id", user.getId());
            List<Annex5> annex5List = annex5Service.list(annex5QueryWrapper);
            
            QueryWrapper<Annex6> annex6QueryWrapper = new QueryWrapper<>();
            annex6QueryWrapper.eq("patient_id", user.getId());
            List<Annex6> annex6List = annex6Service.list(annex6QueryWrapper);
            
            QueryWrapper<Customize> customizeQueryWrapper = new QueryWrapper<>();
            customizeQueryWrapper.eq("patient_id", user.getId());
            List<Customize> customizeList = customizeService.list(customizeQueryWrapper);
            
            QueryWrapper<Annex7> annex7QueryWrapper = new QueryWrapper<>();
            annex7QueryWrapper.eq("patient_id", user.getId());
            List<Annex7> annex7List = annex7Service.list(annex7QueryWrapper);
            if(ObjectUtil.isEmpty(annex1List) && ObjectUtil.isEmpty(annex2List) && ObjectUtil.isEmpty(annex4List) &&
                ObjectUtil.isEmpty(annex5List) && ObjectUtil.isEmpty(annex6List) && ObjectUtil.isEmpty(customizeList) &&
                ObjectUtil.isEmpty(annex7List)){
                result.add(user);
            }
        }
        return result;
    }

    @ApiOperation(value = "根据社区医疗机构id查询已完成的用户列表", notes = "根据社区医疗机构id查询已完成的用户列表")
    @GetMapping(value = "/queryCompleted", params = {"institutionId"})
    public List<User> queryCompleted(int institutionId){
        List<User> result = new ArrayList<>();
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.eq("institution_id", institutionId);
        userQueryWrapper.eq("role",Constants.USER_ROLE_PATIENT);
        List<User> userList = userService.list(userQueryWrapper);
        for(User user: userList){
            QueryWrapper<Annex7> annex7QueryWrapper = new QueryWrapper<>();
            annex7QueryWrapper.eq("patient_id", user.getId());
            List<Annex7> annex7List = annex7Service.list(annex7QueryWrapper);
            if(ObjectUtil.isNotEmpty(annex7List)){
                result.add(user);
            }
        }
        return result;
    }


    @Resource
    ComparativeService comparativeService;

    // 个人中心数据
    @ApiOperation(value = "根据社区医疗机构id查询个人中心数据", notes = "根据社区医疗机构id查询个人中心数据")
    @GetMapping(value = "/queryPersonalCenterData", params = {"institutionId"})
    public PersonalCenterVo queryPersonalCenterData(int institutionId){
        PersonalCenterVo personalCenterVo = new PersonalCenterVo();
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.eq("institution_id", institutionId);
        userQueryWrapper.eq("role", Constants.USER_ROLE_PATIENT);
        List<User> userList = userService.list(userQueryWrapper);
        personalCenterVo.setManagementTeamCount(userList.size());
        int endManagementCount = 0;
        for(User u : userList){
            if(ObjectUtil.isNotEmpty(u.getEndManagement()) && u.getEndManagement()){
                ++endManagementCount;
            }
        }
        personalCenterVo.setManagementTeamEndManagementCount(endManagementCount);
        personalCenterVo.setManagementTeamInManagementCount(personalCenterVo.getManagementTeamCount() - personalCenterVo.getManagementTeamEndManagementCount());
        personalCenterVo.setManagementTeamUnCompletedCount(queryToBeContinued(institutionId).size());
        personalCenterVo.setManagementTeamCompletedCount(queryCompleted(institutionId).size());

        QueryWrapper<Comparative> comparativeQueryWrapper = new QueryWrapper<>();
        comparativeQueryWrapper.eq("institution_id", institutionId);
        List<Comparative> comparativeList = comparativeService.list(comparativeQueryWrapper);
        personalCenterVo.setComparativeTeamCount(comparativeList.size());
        personalCenterVo.setComparativeTeamEarlyCount(comparativeList.stream().filter((item) -> item.getRole().equals("0")).toArray().length);
        personalCenterVo.setComparativeTeamDiabetesCount(personalCenterVo.getComparativeTeamCount() - personalCenterVo.getComparativeTeamEarlyCount());
        return personalCenterVo;
    }


    @ApiOperation(value = "开具处方", notes = "开具处方")
    @PostMapping("/savePrescription")
    public void savePrescription(@RequestBody PrescriptionVo prescriptionVo){
        Date now = new Date();
        User loginUser = getLoginUser();
        // 保存健康教育处方
        PrescriptionHealthEducation prescriptionHealthEducation = prescriptionVo.getPrescriptionHealthEducation();
        QueryWrapper<PrescriptionHealthEducation> healthEducationQueryWrapper = new QueryWrapper<>();
        healthEducationQueryWrapper.eq("patient_id",prescriptionHealthEducation.getPatientId());
        healthEducationQueryWrapper.orderByDesc("number");
        List<PrescriptionHealthEducation> healthEducationList = prescriptionHealthEducationService.list(healthEducationQueryWrapper);
        if(ObjectUtil.isEmpty(healthEducationList)){
            prescriptionHealthEducation.setNumber(1);
        }else{
            prescriptionHealthEducation.setNumber(healthEducationList.get(0).getNumber() + 1);
        }

        prescriptionHealthEducation.setCreateTime(now);
        prescriptionHealthEducation.setCreateUser(loginUser.getName());
        prescriptionHealthEducation.setUpdateTime(now);
        prescriptionHealthEducation.setUpdateUser(loginUser.getName());
        prescriptionHealthEducationService.save(prescriptionHealthEducation);

        // 保存膳食处方
        PrescriptionDiet prescriptionDiet = prescriptionVo.getPrescriptionDiet();
        QueryWrapper<PrescriptionDiet> dietQueryWrapper = new QueryWrapper<>();
        dietQueryWrapper.eq("patient_id",prescriptionDiet.getPatientId());
        dietQueryWrapper.orderByDesc("number");
        List<PrescriptionDiet> prescriptionDietList = prescriptionDietService.list(dietQueryWrapper);
        if(ObjectUtil.isEmpty(prescriptionDietList)){
            prescriptionDiet.setNumber(1);
        }else{
            prescriptionDiet.setNumber(prescriptionDietList.get(0).getNumber() + 1);
        }
        prescriptionDiet.setCreateTime(now);
        prescriptionDiet.setCreateUser(loginUser.getName());
        prescriptionDiet.setUpdateTime(now);
        prescriptionDiet.setUpdateUser(loginUser.getName());
        prescriptionDietService.save(prescriptionDiet);

        // 保存减重处方
        PrescriptionLoseWeight prescriptionLoseWeight = prescriptionVo.getPrescriptionLoseWeight();
        QueryWrapper<PrescriptionLoseWeight> loseWeightQueryWrapper = new QueryWrapper<>();
        loseWeightQueryWrapper.eq("patient_id",prescriptionLoseWeight.getPatientId());
        loseWeightQueryWrapper.orderByDesc("number");
        List<PrescriptionLoseWeight> loseWeightList = prescriptionLoseWeightService.list(loseWeightQueryWrapper);
        if(ObjectUtil.isEmpty(loseWeightList)){
            prescriptionLoseWeight.setNumber(1);
        }else{
            prescriptionLoseWeight.setNumber(loseWeightList.get(0).getNumber() + 1);
        }
        prescriptionLoseWeight.setCreateTime(now);
        prescriptionLoseWeight.setCreateUser(loginUser.getName());
        prescriptionLoseWeight.setUpdateTime(now);
        prescriptionLoseWeight.setUpdateUser(loginUser.getName());
        prescriptionLoseWeightService.save(prescriptionLoseWeight);

        // 保存运动处方
        PrescriptionMotion prescriptionMotion = prescriptionVo.getPrescriptionMotion();
        QueryWrapper<PrescriptionMotion> prescriptionMotionQueryWrapper = new QueryWrapper<>();
        prescriptionMotionQueryWrapper.eq("patient_id",prescriptionLoseWeight.getPatientId());
        prescriptionMotionQueryWrapper.orderByDesc("number");
        List<PrescriptionMotion> motionList = prescriptionMotionService.list(prescriptionMotionQueryWrapper);
        if(ObjectUtil.isEmpty(motionList)){
            prescriptionMotion.setNumber(1);
        }else{
            prescriptionMotion.setNumber(motionList.get(0).getNumber() + 1);
        }
        prescriptionMotion.setCreateTime(now);
        prescriptionMotion.setCreateUser(loginUser.getName());
        prescriptionMotion.setUpdateTime(now);
        prescriptionMotion.setUpdateUser(loginUser.getName());
        prescriptionMotionService.save(prescriptionMotion);

        //保存其他处方
        PrescriptionOther prescriptionOther = prescriptionVo.getPrescriptionOther();
        QueryWrapper<PrescriptionOther> otherQueryWrapper = new QueryWrapper<>();
        otherQueryWrapper.eq("patient_id",prescriptionLoseWeight.getPatientId());
        otherQueryWrapper.orderByDesc("number");
        List<PrescriptionOther> otherList = prescriptionOtherService.list(otherQueryWrapper);
        if(ObjectUtil.isEmpty(otherList)){
            prescriptionOther.setNumber(1);
        }else{
            prescriptionOther.setNumber(otherList.get(0).getNumber() + 1);
        }
        prescriptionOther.setCreateTime(now);
        prescriptionOther.setCreateUser(loginUser.getName());
        prescriptionOther.setUpdateTime(now);
        prescriptionOther.setUpdateUser(loginUser.getName());
        prescriptionOtherService.save(prescriptionOther);

        // 保存体质改善处方
        PrescriptionPhysique prescriptionPhysique = prescriptionVo.getPrescriptionPhysique();
        QueryWrapper<PrescriptionPhysique> physiqueQueryWrapper = new QueryWrapper<>();
        physiqueQueryWrapper.eq("patient_id",prescriptionLoseWeight.getPatientId());
        physiqueQueryWrapper.orderByDesc("number");
        List<PrescriptionPhysique> physiqueList = prescriptionPhysiqueService.list(physiqueQueryWrapper);
        if(ObjectUtil.isEmpty(physiqueList)){
            prescriptionPhysique.setNumber(1);
        }else{
            prescriptionPhysique.setNumber(physiqueList.get(0).getNumber() + 1);
        }
        prescriptionPhysique.setCreateTime(now);
        prescriptionPhysique.setCreateUser(loginUser.getName());
        prescriptionPhysique.setUpdateTime(now);
        prescriptionPhysique.setUpdateUser(loginUser.getName());
        prescriptionPhysiqueService.save(prescriptionPhysique);

        // 保存睡眠管理处方
        PrescriptionSleep prescriptionSleep = prescriptionVo.getPrescriptionSleep();
        QueryWrapper<PrescriptionSleep> sleepQueryWrapper = new QueryWrapper<>();
        sleepQueryWrapper.eq("patient_id",prescriptionLoseWeight.getPatientId());
        sleepQueryWrapper.orderByDesc("number");
        List<PrescriptionSleep> sleepList = prescriptionSleepService.list(sleepQueryWrapper);
        if(ObjectUtil.isEmpty(sleepList)){
            prescriptionSleep.setNumber(1);
        }else{
            prescriptionSleep.setNumber(sleepList.get(0).getNumber() + 1);
        }
        prescriptionSleep.setCreateTime(now);
        prescriptionSleep.setCreateUser(loginUser.getName());
        prescriptionSleep.setUpdateTime(now);
        prescriptionSleep.setUpdateUser(loginUser.getName());
        prescriptionSleepService.save(prescriptionSleep);
    }


    @Resource
    HttpServletResponse response;

    /*
    @RateLimit()
    @ApiOperation(value = "导出：结束管理-糖前期管理组数据表", notes = "结束管理-糖前期管理组数据表")
//    @GetMapping(value = "/exportStopManageUserPreSugar", params = {"institutionId"})
    @GetMapping(value = "/exportStopManageUserPreSugar")
    public void exportStopManageUserPreSugar() throws ClassNotFoundException, IllegalAccessException, IOException {
        // 查询出需要导出的数据
        // 查询用户
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        // userQueryWrapper.eq("institution_id",institutionId);
        userQueryWrapper.eq("role","patient");
        userQueryWrapper.eq("end_management", true);
        userQueryWrapper.orderByAsc("id");
        List<User> patientList = userService.list(userQueryWrapper);
        // 查询糖前期患者生活方式调查问卷
        QueryWrapper<Annex2> annex2QueryWrapper = new QueryWrapper<>();
        List<Integer> patientIdList = patientList.stream().map(User::getId).collect(Collectors.toList());
        patientIdList.add(-1);
        annex2QueryWrapper.in("patient_id", patientIdList);
        annex2QueryWrapper.eq("the_last_time", true);
        List<Annex2> annex2List = annex2Service.list(annex2QueryWrapper);
        // 创建excel
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("糖尿病前期患者生活方式调查问卷");
        Row row = sheet.createRow(0);
        Cell cell;
        // 输入标题
        String [] title = {"姓名","id","患者id","出生日期","性别","婚姻状况","文化程度","职业" ,"是否吸烟","吸烟量","烟龄","戒烟情况","是否经常饮酒","饮酒频率" ,"一次喝多少（白酒>=42°）" ,"一次喝多少（白酒<42°）","一次喝多少（啤酒4°）","一次喝多少（葡萄酒10°）","一天几顿饭","一周内在家吃早餐天数","一周内在食堂吃早餐天数","一周内在餐馆吃早餐天数","一周内在家吃午餐天数","一周内在食堂吃午餐天数","一周内在餐馆吃午餐天数","一周内在家吃晚餐天数","一周内在食堂吃晚餐天数","一周内在餐馆吃晚餐天数","主食（大米、馒头、面条）食用量" ,"其他主食（粗杂粮、薯类）食用量","肉鱼海鲜食用量","鸡蛋","牛奶","蔬菜" ,"水果","油脂类（食用油）","盐是否过多" ,"喜欢的饮料","是否有持续10分钟的中强度活动","中强度活动每周几次" ,"日常是否健身","有哪些体育活动" ,"每周运动几次","运动几分钟" ,"静坐时间","睡眠时间-时" ,"睡眠时间-分" ,"睡眠质量","经常性失眠" ,"睡眠不沉" ,"入睡困难" ,"易早醒，难再入睡" ,"有时彻夜难眠" ,"体重状况如何","是否控制过体重" ,"最近一次测量血压的时间","是否知道自己血压情况" ,"有无被诊断过高血压","最近一次测量血糖的时间" ,"是否知道自己血糖情况","有无被诊断患有糖尿病" ,"最近一次测量血脂的时间","有无被诊断为血脂异常或高血脂" ,"第几次录入","添加时间","添加用户","更新时间","更新用户","是否是末次录入"};
        String [] title2 = {"id","patientId", "a1", "a2", "a3", "a4", "a5",  "b1", "b21", "b22", "b3", "b4", "b5",  "b61",  "b62", "b63", "b64", "d1", "d21", "d22", "d23", "d31", "d32", "d33", "d41", "d42", "d43", "d5",  "d6", "d7", "d8", "d9", "d10",  "d11", "d12", "d13",  "d14", "e1", "e2",  "e3", "e4",  "e5", "e6",  "e7", "e81",  "e82",  "e9", "e101",  "e102",  "e103",  "e104",  "e105",  "f1", "f2",  "f3", "f4",  "f5", "f6",  "f7", "f8",  "f9", "f10", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};

        for(int i = 0; i < title.length;i++){
            cell = row.createCell(i, CellType.STRING);
            cell.setCellValue(title[i]);
        }
        int cellNum;
        int rowCount = 1;
        for(int i = 0; i < patientList.size();i++){
            User patient = patientList.get(i);
            for(Annex2 annex2 : annex2List){
                if(annex2.getPatientId().equals(patient.getId())){
                    row = sheet.createRow(rowCount++);
                    cellNum = 0;
                    cell = row.createCell(cellNum++);
                    cell.setCellValue(patient.getName());
                    Class c = Class.forName("com.huishu.tcm.entity.Annex2");
                    Class parentC = c.getSuperclass();
                    List<Field> fieldList = new ArrayList<>();
                    Field[] fields = c.getDeclaredFields();
                    Field[] parentCFields = parentC.getDeclaredFields();
                    fieldList.addAll(Arrays.asList(fields));
                    fieldList.addAll(Arrays.asList(parentCFields));
                    for(String t :title2){
                        cell = row.createCell(cellNum++);
                        for(Field f :fieldList){
                            if(t.equals(f.getName())){
                                f.setAccessible(true);
                                if("createTime".equals(t) || "updateTime".equals(t)){
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    cell.setCellValue(f.get(annex2) == null?"":sdf.format(f.get(annex2)));
                                }else{
                                    cell.setCellValue(f.get(annex2) == null?"":f.get(annex2).toString());
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }

        // 查询身体测量及静脉血筛查记录表
        QueryWrapper<Annex4> annex4QueryWrapper = new QueryWrapper<>();
        patientIdList = patientList.stream().map(User::getId).collect(Collectors.toList());
        patientIdList.add(-1);
        annex4QueryWrapper.in("patient_id", patientIdList);
        annex4QueryWrapper.eq("the_last_time", true);
        List<Annex4> annex4List = annex4Service.list(annex4QueryWrapper);
        String[] title3 = { "姓名","id", "患者id", "身高", "体重", "腰围", "臀围", "收缩压", "舒张压", "心率", "血红蛋白", "白细胞", "血小板", "其他", "空腹血糖（mmol/L）", "空腹血糖（mg/dL）", "糖负荷后2h血糖（OGTT）", "糖化血红蛋白（空腹）", "血清谷丙转氨酶U/L", "血清谷草转氨酶mg/dL", "结合胆红素umol/L", "总胆红素mg/dL", "白蛋白g/L", "血清肌酐umol/L",  "血钾浓度mmol/L",  "血尿素mmol/L",  "血钠浓度mmol/L",  "总胆固醇umol/L",  "甘油三酯mmol/L",  "血清低密度脂蛋白胆固醇mmol/L",  "血清高密度脂蛋白胆固醇mmol/L",  "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户", "是否是末次录入"};
        String[] title4 = { "id", "patientId", "m1", "m2", "m3", "m4", "m5", "m6", "m7", "a11", "a12", "a13", "a14", "a21", "a22", "a23", "a24", "a31", "a32", "a33", "a34", "a35", "a41",  "a42",  "a43",  "a44",  "a51",  "a52",  "a53",  "a54", "number",  "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};
        sheet = workbook.createSheet("身体测量及静脉血筛查记录表");
        row = sheet.createRow(0);
        for(int i = 0; i < title3.length;i++){
            cell = row.createCell(i, CellType.STRING);
            cell.setCellValue(title3[i]);
        }
        rowCount = 1;
        for(int i = 0; i < patientList.size();i++){
            User patient = patientList.get(i);
            for(Annex4 annex4 : annex4List){
                if(annex4.getPatientId().equals(patient.getId())){
                    row = sheet.createRow(rowCount++);
                    cellNum = 0;
                    cell = row.createCell(cellNum++);
                    cell.setCellValue(patient.getName());
                    Class c = Class.forName("com.huishu.tcm.entity.Annex4");
                    Class parentC = c.getSuperclass();
                    List<Field> fieldList = new ArrayList<>();
                    Field[] fields = c.getDeclaredFields();
                    Field[] parentCFields = parentC.getDeclaredFields();
                    fieldList.addAll(Arrays.asList(fields));
                    fieldList.addAll(Arrays.asList(parentCFields));
                    for(String t :title4){
                        cell = row.createCell(cellNum++);
                        for(Field f :fieldList){
                            if(t.equals(f.getName())){
                                f.setAccessible(true);
                                if("createTime".equals(t) || "updateTime".equals(t)){
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    cell.setCellValue(f.get(annex4) == null?"":sdf.format(f.get(annex4)));
                                }else{
                                    cell.setCellValue(f.get(annex4) == null?"":f.get(annex4).toString());
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.close();
        out.close();
    }
*/
    @RateLimit()
    @ApiOperation(value = "导出：结束管理-糖前期管理组数据表", notes = "结束管理-糖前期管理组数据表")
    @GetMapping(value = "/exportStopManageUserPreSugar")
    public void exportStopManageUserPreSugar() throws IllegalAccessException, NoSuchFieldException, IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        // 查询出需要导出的数据
        // 查询用户
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        // userQueryWrapper.eq("institution_id",institutionId);
        userQueryWrapper.eq("role","patient");
        userQueryWrapper.eq("end_management", true);
        userQueryWrapper.orderByAsc("id");
        List<User> patientList = userService.list(userQueryWrapper);

        QueryWrapper queryWrapper = new QueryWrapper();
        List<Integer> patientIdList = patientList.stream().map(User::getId).collect(Collectors.toList());
        patientIdList.add(-1);
        queryWrapper.in("patient_id", patientIdList);
        queryWrapper.eq("the_last_time", true);

        // 1、查询annex6
        List<Annex6> annex6List = annex6Service.list(queryWrapper);
        String [] annex6TitleName = {"姓名","id", "患者id", "倦怠乏力", "胁肋胀痛", "口苦", "口淡", "口腻",  "自汗", "腹胀", "腕闷", "食欲不振", "恶心呕吐",  "烦躁易怒", "腰酸", "便溏", "小便发黄", "面色萎黄",  "面色无华", "肢体困重", "目干", "口干", "耳鸣",  "五心烦热", "午后潮热", "盗汗", "失眠", "便秘",  "肢体凉麻痛", "舌", "苔", "脉象", "中医辨证",  "总分", "证候积分减少比例", "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户", "是否是末次录入"};
        String [] annex6TitleKey = {"id", "patientId", "a1", "a2", "a3", "a4", "a5",  "a6", "a7", "a8", "a9", "a10",  "a11", "a12", "a13", "a14", "a15",  "a16", "a17", "a18", "a19", "a20",  "a21", "a22", "a23", "a24", "a25",  "a26", "a27", "a28", "a29", "a30",  "score", "reducedProportions", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"中医证候积分量表",annex6TitleName,annex6TitleKey,patientList,annex6List);

        // 2、查询annex4
        List<Annex4> annex4List = annex4Service.list(queryWrapper);
        String [] annex4TitleName = {"姓名", "id", "患者id", "身高", "体重", "腰围", "臀围", "收缩压", "舒张压", "心率", "血红蛋白", "白细胞", "血小板", "其他", "空腹血糖（mmol/L）", "空腹血糖（mg/dL）", "糖负荷后2h血糖（OGTT）", "糖化血红蛋白（空腹）", "血清谷丙转氨酶U/L", "血清谷草转氨酶mg/dL", "结合胆红素umol/L", "总胆红素mg/dL", "白蛋白g/L", "血清肌酐umol/L",  "血钾浓度mmol/L",  "血尿素mmol/L",  "血钠浓度mmol/L",  "总胆固醇umol/L",  "甘油三酯mmol/L",  "血清低密度脂蛋白胆固醇mmol/L",  "血清高密度脂蛋白胆固醇mmol/L",  "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户","是否是末次录入"};
        String [] annex4TitleKey = {"id", "patientId","m1", "m2", "m3", "m4", "m5", "m6", "m7", "a11", "a12", "a13", "a14", "a21", "a22", "a23", "a24", "a31", "a32", "a33", "a34", "a35", "a41",  "a42",  "a43",  "a44",  "a51",  "a52",  "a53",  "a54", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"身体测量及静脉血筛查登记表",annex4TitleName,annex4TitleKey,patientList,annex4List);

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.dispose();
        workbook.close();
        out.close();
    }

    @RateLimit()
    @ApiOperation(value = "导出：结束管理-糖前期对照组数据表", notes = "结束管理-糖前期对照组数据表")
    // @GetMapping(value = "/exportStopManageComparativePreSugar", params = {"institutionId"})
    @GetMapping(value = "/exportStopManageComparativePreSugar")
    public void exportStopManageComparativePreSugar() throws ClassNotFoundException, IllegalAccessException, IOException {
        /* 查询出需要导出的数据 */
        // 查询对照组
        QueryWrapper<Comparative> comparativeQueryWrapper = new QueryWrapper<>();
        // comparativeQueryWrapper.eq("institution_id",institutionId);
        comparativeQueryWrapper.eq("end_management", true);
        comparativeQueryWrapper.eq("role", "0");
        comparativeQueryWrapper.orderByAsc("id");
        List<Comparative> patientList = comparativeService.list(comparativeQueryWrapper);
        // 查询糖前期患者生活方式调查问卷
        QueryWrapper<Annex2> annex2QueryWrapper = new QueryWrapper<>();
        List<Integer> patientIdList = patientList.stream().map(Comparative::getId).collect(Collectors.toList());
        patientIdList.add(-1);
        annex2QueryWrapper.in("patient_id", patientIdList);
        annex2QueryWrapper.eq("the_last_time", true);
        List<Annex2> annex2List = annex2Service.list(annex2QueryWrapper);
        // 创建excel
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        SXSSFSheet sheet = workbook.createSheet("糖尿病前期患者生活方式调查问卷");
        Row row = sheet.createRow(0);
        Cell cell;
        // 输入标题
        String [] title = {"姓名","id", "患者id","出生日期","性别","婚姻状况","文化程度","职业" ,"是否吸烟","吸烟量","烟龄","戒烟情况","是否经常饮酒","饮酒频率" ,"一次喝多少（白酒>=42°）" ,"一次喝多少（白酒<42°）","一次喝多少（啤酒4°）","一次喝多少（葡萄酒10°）","一天几顿饭","一周内在家吃早餐天数","一周内在食堂吃早餐天数","一周内在餐馆吃早餐天数","一周内在家吃午餐天数","一周内在食堂吃午餐天数","一周内在餐馆吃午餐天数","一周内在家吃晚餐天数","一周内在食堂吃晚餐天数","一周内在餐馆吃晚餐天数","主食（大米、馒头、面条）食用量" ,"其他主食（粗杂粮、薯类）食用量","肉鱼海鲜食用量","鸡蛋","牛奶","蔬菜" ,"水果","油脂类（食用油）","盐是否过多" ,"喜欢的饮料","是否有持续10分钟的中强度活动","中强度活动每周几次" ,"日常是否健身","有哪些体育活动" ,"每周运动几次","运动几分钟" ,"静坐时间","睡眠时间-时" ,"睡眠时间-分" ,"睡眠质量","经常性失眠" ,"睡眠不沉" ,"入睡困难" ,"易早醒，难再入睡" ,"有时彻夜难眠" ,"体重状况如何","是否控制过体重" ,"最近一次测量血压的时间","是否知道自己血压情况" ,"有无被诊断过高血压","最近一次测量血糖的时间" ,"是否知道自己血糖情况","有无被诊断患有糖尿病" ,"最近一次测量血脂的时间","有无被诊断为血脂异常或高血脂" ,"第几次录入","添加时间","添加用户","更新时间","更新用户","是否是末次录入"};
        String [] title2 = {"id", "patientId", "a1", "a2", "a3", "a4", "a5",  "b1", "b21", "b22", "b3", "b4", "b5",  "b61",  "b62", "b63", "b64", "d1", "d21", "d22", "d23", "d31", "d32", "d33", "d41", "d42", "d43", "d5",  "d6", "d7", "d8", "d9", "d10",  "d11", "d12", "d13",  "d14", "e1", "e2",  "e3", "e4",  "e5", "e6",  "e7", "e81",  "e82",  "e9", "e101",  "e102",  "e103",  "e104",  "e105",  "f1", "f2",  "f3", "f4",  "f5", "f6",  "f7", "f8",  "f9", "f10", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};

        for(int i = 0; i < title.length;i++){
            cell = row.createCell(i, CellType.STRING);
            cell.setCellValue(title[i]);
        }
        int cellNum;
        int rowCount = 1;
        for(int i = 0; i < patientList.size();i++){
            Comparative patient = patientList.get(i);
            for(Annex2 annex2 : annex2List){
                if(annex2.getPatientId().equals(patient.getId())){
                    row = sheet.createRow(rowCount++);
                    cellNum = 0;
                    cell = row.createCell(cellNum++);
                    cell.setCellValue(patient.getName());
                    Class c = Class.forName("com.huishu.tcm.entity.Annex2");
                    Class parentC = c.getSuperclass();
                    List<Field> fieldList = new ArrayList<>();
                    Field[] fields = c.getDeclaredFields();
                    Field[] parentCFields = parentC.getDeclaredFields();
                    fieldList.addAll(Arrays.asList(fields));
                    fieldList.addAll(Arrays.asList(parentCFields));
                    for(String t :title2){
                        cell = row.createCell(cellNum++);
                        for(Field f :fieldList){
                            if(t.equals(f.getName())){
                                f.setAccessible(true);
                                if("createTime".equals(t) || "updateTime".equals(t)){
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    cell.setCellValue(f.get(annex2) == null?"":sdf.format(f.get(annex2)));
                                }else{
                                    cell.setCellValue(f.get(annex2) == null?"":f.get(annex2).toString());
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }

        // 查询身体测量及静脉血筛查记录表
        QueryWrapper<Annex4> annex4QueryWrapper = new QueryWrapper<>();
        patientIdList = patientList.stream().map(Comparative::getId).collect(Collectors.toList());
        patientIdList.add(-1);
        annex4QueryWrapper.in("patient_id", patientIdList);
        annex4QueryWrapper.eq("the_last_time", true);
        List<Annex4> annex4List = annex4Service.list(annex4QueryWrapper);
        String[] title3 = { "姓名","id",  "患者id", "身高", "体重", "腰围", "臀围", "收缩压", "舒张压", "心率", "血红蛋白", "白细胞", "血小板", "其他", "空腹血糖（mmol/L）", "空腹血糖（mg/dL）", "糖负荷后2h血糖（OGTT）", "糖化血红蛋白（空腹）", "血清谷丙转氨酶U/L", "血清谷草转氨酶mg/dL", "结合胆红素umol/L", "总胆红素mg/dL", "白蛋白g/L", "血清肌酐umol/L",  "血钾浓度mmol/L",  "血尿素mmol/L",  "血钠浓度mmol/L",  "总胆固醇umol/L",  "甘油三酯mmol/L",  "血清低密度脂蛋白胆固醇mmol/L",  "血清高密度脂蛋白胆固醇mmol/L",  "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户", "是否是末次录入"};
        String[] title4 = { "id", "patientId", "m1", "m2", "m3", "m4", "m5", "m6", "m7", "a11", "a12", "a13", "a14", "a21", "a22", "a23", "a24", "a31", "a32", "a33", "a34", "a35", "a41",  "a42",  "a43",  "a44",  "a51",  "a52",  "a53",  "a54", "number",  "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};
        sheet = workbook.createSheet("身体测量及静脉血筛查记录表");
        row = sheet.createRow(0);
        for(int i = 0; i < title3.length;i++){
            cell = row.createCell(i, CellType.STRING);
            cell.setCellValue(title3[i]);
        }
        rowCount = 1;
        for(int i = 0; i < patientList.size();i++){
            Comparative patient = patientList.get(i);
            for(Annex4 annex4 : annex4List){
                if(annex4.getPatientId().equals(patient.getId())){
                    row = sheet.createRow(rowCount++);
                    cellNum = 0;
                    cell = row.createCell(cellNum++);
                    cell.setCellValue(patient.getName());
                    Class c = Class.forName("com.huishu.tcm.entity.Annex4");
                    Class parentC = c.getSuperclass();
                    List<Field> fieldList = new ArrayList<>();
                    Field[] fields = c.getDeclaredFields();
                    Field[] parentCFields = parentC.getDeclaredFields();
                    fieldList.addAll(Arrays.asList(fields));
                    fieldList.addAll(Arrays.asList(parentCFields));
                    for(String t :title4){
                        cell = row.createCell(cellNum++);
                        for(Field f :fieldList){
                            if(t.equals(f.getName())){
                                f.setAccessible(true);
                                if("createTime".equals(t) || "updateTime".equals(t)){
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    cell.setCellValue(f.get(annex4) == null?"":sdf.format(f.get(annex4)));
                                }else{
                                    cell.setCellValue(f.get(annex4) == null?"":f.get(annex4).toString());
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.close();
        out.close();
    }

    @Resource
    Annex9Service annex9Service;

    @RateLimit()
    @ApiOperation(value = "导出：结束管理-糖尿病对照组数据表", notes = "结束管理-糖尿病对照组数据表")
//    @GetMapping(value = "/exportStopManageComparativeDiabetes", params = {"institutionId"})
    @GetMapping(value = "/exportStopManageComparativeDiabetes")
    public void exportStopManageComparativeDiabetes() throws ClassNotFoundException, IllegalAccessException, IOException   {
        /* 查询出需要导出的数据 */
        // 查询对照组
        QueryWrapper<Comparative> comparativeQueryWrapper = new QueryWrapper<>();
        // comparativeQueryWrapper.eq("institution_id",institutionId);
        comparativeQueryWrapper.eq("end_management", true);
        comparativeQueryWrapper.eq("role","1");
        comparativeQueryWrapper.orderByAsc("id");
        List<Comparative> patientList = comparativeService.list(comparativeQueryWrapper);
        // 查询糖尿病患者随访中医干预记录表
        QueryWrapper<Annex9> annex9QueryWrapper = new QueryWrapper<>();
        List<Integer> patientIdList = patientList.stream().map(Comparative::getId).collect(Collectors.toList());
        patientIdList.add(-1);
        annex9QueryWrapper.in("patient_id", patientIdList);
        annex9QueryWrapper.eq("the_last_time", true);
        List<Annex9> annex9List = annex9Service.list(annex9QueryWrapper);
        // 创建excel
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        SXSSFSheet sheet = workbook.createSheet("糖尿病患者中医干预随访记录");
        Row row = sheet.createRow(0);
        Cell cell;
        // 输入标题
        String [] title = {"姓名", "id", "患者id", "情志调摄", "饮食调养", "起居调摄", "运动保健", "穴位保健", "其他", "身高", "体重", "腰围", "臀围", "血常规-血红蛋白", "血常规-白细胞", "血常规-血小板", "空腹血糖mmol/L", "空腹血糖mg/dL", "糖化血红蛋白", "肝功能-血清谷丙转氨酶", "肝功能-血清谷草转氨酶", "肝功能-结合胆红素", "肝功能-总胆红素", "肝功能-白蛋白", "肾功能-血清肌酐", "肾功能-血钾浓度", "肾功能-血尿素", "肾功能-血钠浓度", "血脂-总胆固醇", "血脂-甘油三酯", "血脂-血清低密度脂蛋白胆固醇", "血脂-血清高密度脂蛋白胆固醇", "第几次录入", "是否是末次录入", "添加时间", "添加用户", "更新时间", "更新用户"};
        String [] title2 = {"id", "patientId", "a11", "a12", "a13", "a14", "a15", "a16", "a21", "a22", "a23", "a24", "a311", "a312", "a313", "a321", "a322", "a331", "a341", "a342", "a343", "a344", "a345", "a351", "a352", "a353", "a354", "a361", "a362", "a363", "a364", "number", "theLastTime", "createTime", "createUser", "updateTime", "updateUser"};

        for(int i = 0; i < title.length;i++){
            cell = row.createCell(i, CellType.STRING);
            cell.setCellValue(title[i]);
        }
        int cellNum;
        int rowCount = 1;
        for(int i = 0; i < patientList.size();i++){
            Comparative patient = patientList.get(i);
            for(Annex9 annex9 : annex9List){
                if(annex9.getPatientId().equals(patient.getId())){
                    row = sheet.createRow(rowCount++);
                    cellNum = 0;
                    cell = row.createCell(cellNum++);
                    cell.setCellValue(patient.getName());
                    Class c = Class.forName("com.huishu.tcm.entity.Annex9");
                    Class parentC = c.getSuperclass();
                    List<Field> fieldList = new ArrayList<>();
                    Field[] fields = c.getDeclaredFields();
                    Field[] parentCFields = parentC.getDeclaredFields();
                    fieldList.addAll(Arrays.asList(fields));
                    fieldList.addAll(Arrays.asList(parentCFields));
                    for(String t :title2){
                        cell = row.createCell(cellNum++);
                        for(Field f :fieldList){
                            if(t.equals(f.getName())){
                                f.setAccessible(true);
                                if("createTime".equals(t) || "updateTime".equals(t)){
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    cell.setCellValue(f.get(annex9) == null?"":sdf.format(f.get(annex9)));
                                }else{
                                    cell.setCellValue(f.get(annex9) == null?"":f.get(annex9).toString());
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.close();
        out.close();
    }


    @Resource
    Annex7PatientService annex7PatientService;

    @RateLimit()
    @ApiOperation(value = "导出：附件1到附件9", notes = "导出附件1到附件9")
    @GetMapping(value = "/exportAnnex1ToAnnex9")
    public void exportAnnex1ToAnnex9() throws IllegalAccessException, ClassNotFoundException, NoSuchFieldException, IOException {
        // 查询管理组用户数据
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.eq("role", "patient");
        List<User> patientList = userService.list(userQueryWrapper);
        // 查询对照组用户数据
        QueryWrapper<Comparative> comparativeQueryWrapper = new QueryWrapper<>();
        List<Comparative> comparativeList = comparativeService.list(comparativeQueryWrapper);
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        String[] userTitleName = {"用户id","姓名", "手机号","性别","年龄","身份证号","是否已结束管理","基层医疗机构id","添加时间","添加用户","更新时间","更新用户"};
        String[] userTitleKey = {"id","name","telNum","gender","age","idNum","endManagement","institutionId","createTime","createUser","updateTime","updateUser"};
        exportUserAndComparativeExcelMethod(workbook,"管理组用户数据",userTitleName,userTitleKey,patientList);
        exportUserAndComparativeExcelMethod(workbook,"对照组用户数据",userTitleName,userTitleKey,comparativeList);
        // 将对照组数据和管理组数据合并，方便导出后续表格使用
        ObjUtil.comparative2User(patientList, comparativeList);
        // 1、查询annex1
        QueryWrapper queryWrapper = new QueryWrapper();
        List<Integer> patientIdList = patientList.stream().map(User::getId).collect(Collectors.toList());
        patientIdList.add(-1);
        queryWrapper.in("patient_id", patientIdList);
        List<Annex1> annex1List = annex1Service.list(queryWrapper);
        String[] annex1TitleName = {"姓名","id", "患者id","脾胃壅滞证","湿热蕴结证","脾虚痰湿证","肝郁气滞证","气阴两虚证","第几次录入","添加时间","添加用户","更新时间","更新用户"};
        String[] annex1TitleKey = {"id", "patientId","a1","a2","a3","a4","a5","number","createTime","createUser","updateTime","updateUser"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"糖尿病前期中医辩证标准及诊断记录",annex1TitleName,annex1TitleKey,patientList,annex1List);
        // 2、查询annex2
        List<Annex2> annex2List = annex2Service.list(queryWrapper);
        String [] annex2TitleName = {"姓名","id", "患者id","出生日期","性别","婚姻状况","文化程度","职业" ,"是否吸烟","吸烟量","烟龄","戒烟情况","是否经常饮酒","饮酒频率" ,"一次喝多少（白酒>=42°）" ,"一次喝多少（白酒<42°）","一次喝多少（啤酒4°）","一次喝多少（葡萄酒10°）","一天几顿饭","一周内在家吃早餐天数","一周内在食堂吃早餐天数","一周内在餐馆吃早餐天数","一周内在家吃午餐天数","一周内在食堂吃午餐天数","一周内在餐馆吃午餐天数","一周内在家吃晚餐天数","一周内在食堂吃晚餐天数","一周内在餐馆吃晚餐天数","主食（大米、馒头、面条）食用量" ,"其他主食（粗杂粮、薯类）食用量","肉鱼海鲜食用量","鸡蛋","牛奶","蔬菜" ,"水果","油脂类（食用油）","盐是否过多" ,"喜欢的饮料","是否有持续10分钟的中强度活动","中强度活动每周几次" ,"日常是否健身","有哪些体育活动" ,"每周运动几次","运动几分钟" ,"静坐时间","睡眠时间-时" ,"睡眠时间-分" ,"睡眠质量","经常性失眠" ,"睡眠不沉" ,"入睡困难" ,"易早醒，难再入睡" ,"有时彻夜难眠" ,"体重状况如何","是否控制过体重" ,"最近一次测量血压的时间","是否知道自己血压情况" ,"有无被诊断过高血压","最近一次测量血糖的时间" ,"是否知道自己血糖情况","有无被诊断患有糖尿病" ,"最近一次测量血脂的时间","有无被诊断为血脂异常或高血脂" ,"第几次录入","添加时间","添加用户","更新时间","更新用户","是否是末次录入"};
        String [] annex2TitleKey = {"id", "patientId", "a1", "a2", "a3", "a4", "a5",  "b1", "b21", "b22", "b3", "b4", "b5",  "b61",  "b62", "b63", "b64", "d1", "d21", "d22", "d23", "d31", "d32", "d33", "d41", "d42", "d43", "d5",  "d6", "d7", "d8", "d9", "d10",  "d11", "d12", "d13",  "d14", "e1", "e2",  "e3", "e4",  "e5", "e6",  "e7", "e81",  "e82",  "e9", "e101",  "e102",  "e103",  "e104",  "e105",  "f1", "f2",  "f3", "f4",  "f5", "f6",  "f7", "f8",  "f9", "f10", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"糖尿病前期患者生活方式调查问卷",annex2TitleName,annex2TitleKey,patientList,annex2List);
        // 3、查询annex4
        List<Annex4> annex4List = annex4Service.list(queryWrapper);
        String [] annex4TitleName = {"姓名", "id", "患者id", "身高", "体重", "腰围", "臀围", "收缩压", "舒张压", "心率", "血红蛋白", "白细胞", "血小板", "其他", "空腹血糖（mmol/L）", "空腹血糖（mg/dL）", "糖负荷后2h血糖（OGTT）", "糖化血红蛋白（空腹）", "血清谷丙转氨酶U/L", "血清谷草转氨酶mg/dL", "结合胆红素umol/L", "总胆红素mg/dL", "白蛋白g/L", "血清肌酐umol/L",  "血钾浓度mmol/L",  "血尿素mmol/L",  "血钠浓度mmol/L",  "总胆固醇umol/L",  "甘油三酯mmol/L",  "血清低密度脂蛋白胆固醇mmol/L",  "血清高密度脂蛋白胆固醇mmol/L",  "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户","是否是末次录入"};
        String [] annex4TitleKey = {"id", "patientId","m1", "m2", "m3", "m4", "m5", "m6", "m7", "a11", "a12", "a13", "a14", "a21", "a22", "a23", "a24", "a31", "a32", "a33", "a34", "a35", "a41",  "a42",  "a43",  "a44",  "a51",  "a52",  "a53",  "a54", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"身体测量及静脉血筛查登记表",annex4TitleName,annex4TitleKey,patientList,annex4List);
        // 4、查询annex5
        List<Annex5> annex5List = annex5Service.list(queryWrapper);
        String [] annex5TitleName = {"姓名", "id", "患者id", "精力充沛", "容易疲乏", "呼吸短促，接不上气", "说话低弱无力", "闷闷不乐，情绪低沉",  "精神紧张，焦虑不安", "因生活状态改变而孤独、低落", "容易害怕或受到惊吓", "感到身体超重不轻松", "眼睛干涩",  "手脚发凉", "胃脘部、背部或腰膝部怕冷", "耐受不了寒冷", "容易感冒", "没有感冒时鼻塞、流鼻涕",  "口粘口腻或睡觉打鼾", "容易过敏", "皮肤易起荨麻疹", "皮肤在不知不觉中出现青紫瘀斑、皮下出血", "皮肤一抓就红，并出现抓痕",  "皮肤或口唇干", "肢体麻木或固定部位疼痛", "面部或鼻部有油腻感或者油亮发光", "面色或目眶晦黯，或出现褐色斑块/斑点", "皮肤湿疹、疮疖",  "口干咽燥、总想喝水", "口苦或嘴里有异味", "腹部肥大", "吃(喝)凉的东西会感到不舒服或者怕吃（喝）凉的东西", "大便黏滞不爽、解不尽",  "大便干燥", "舌苔厚腻或有舌苔厚厚的感觉", "舌下静脉瘀紫或增粗", "气虚质", "阳虚质",  "阴虚质",  "痰湿质",  "湿热质",  "血瘀质",  "气郁质",  "特禀质",  "平和质", "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户", "气虚质得分", "阳虚质得分", "阴虚质得分", "痰湿质得分", "湿热质得分", "血瘀质得分", "气郁质得分", "特禀质得分", "平和质得分"};
        String [] annex5TitleKey = {"id", "patientId", "a1", "a2", "a3", "a4", "a5",  "a6", "a7", "a8", "a9", "a10",  "a11", "a12", "a13", "a14", "a15",  "a16", "a17", "a18", "a19", "a20",  "a21", "a22", "a23", "a24", "a25",  "a26", "a27", "a28", "a29", "a30",  "a31", "a32", "a33", "a34", "a35",  "a36",  "a37",  "a38",  "a39",  "a40",  "a41",  "a42", "number", "createTime", "createUser", "updateTime", "updateUser", "a341", "a351", "a361", "a371", "a381", "a391", "a401", "a411", "a421"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"中医体质辨识表",annex5TitleName,annex5TitleKey,patientList,annex5List);
        // 5、查询annex6
        List<Annex6> annex6List = annex6Service.list(queryWrapper);
        String [] annex6TitleName = {"姓名","id", "患者id", "倦怠乏力", "胁肋胀痛", "口苦", "口淡", "口腻",  "自汗", "腹胀", "腕闷", "食欲不振", "恶心呕吐",  "烦躁易怒", "腰酸", "便溏", "小便发黄", "面色萎黄",  "面色无华", "肢体困重", "目干", "口干", "耳鸣",  "五心烦热", "午后潮热", "盗汗", "失眠", "便秘",  "肢体凉麻痛", "舌", "苔", "脉象", "中医辨证",  "总分", "证候积分减少比例", "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户", "是否是末次录入"};
        String [] annex6TitleKey = {"id", "patientId", "a1", "a2", "a3", "a4", "a5",  "a6", "a7", "a8", "a9", "a10",  "a11", "a12", "a13", "a14", "a15",  "a16", "a17", "a18", "a19", "a20",  "a21", "a22", "a23", "a24", "a25",  "a26", "a27", "a28", "a29", "a30",  "score", "reducedProportions", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"中医证候积分量表",annex6TitleName,annex6TitleKey,patientList,annex6List);
        // 6、查询annex7
        List<Annex7> annex7List = annex7Service.list(queryWrapper);
        String [] annex7TitleName = {"姓名","id", "患者id", "身高", "体重", "腰围", "臀围", "收缩压",     "舒张压", "空腹血糖mmol/L",     "空腹血糖mg/dL", "健康教育处方-是否了解改善生活方式对改善糖尿病前期有积极意义", "健康教育处方-教授的中医防病技巧是否能熟练运用", "健康教育处方-是否能熟练使用血糖仪自测血糖", "膳食营养-是否注意合理膳食、均衡营养", "膳食营养-是否按照“500111法则”或“手掌法则”执行情况", "膳食营养-推荐中医食疗措施执行情况", "运动管理-有氧运动方式", "运动管理-有氧运动频次", "运动管理-有氧每次运动时长", "运动管理-阻抗运动方式", "运动管理-阻抗运动频次", "运动管理-阻抗每次运动时长", "运动管理-中医运动方式", "运动管理-中医运动频次", "运动管理-中医每次运动时长", "运动管理-其他中医运动管理措施执行情况", "体重管理-体重减轻", "体重管理-主动控制饮食", "体重管理-主动增强运动", "体重管理-中医减重执行情况", "睡眠管理情况-睡眠时间6-8小时", "偏颇体质调理-是否按处方落实调理", "医生开具的其他中医药血糖控制处方-是否按照中医血糖控制处方严格执行", "形-", "神-", "纳-", "眠-", "便-", "下一步指导意见", "是否已退出管理", "退出原因", "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户", "是否是末次录入", "膳食营养-其他中医药管理措施执行情况"};
        String [] annex7TitleKey = {"id", "patientId","a11", "a12", "a13", "a14", "a15",     "a16", "a17",     "a18", "a211", "a212", "a213", "a221", "a222", "a223", "a231", "a232", "a233", "a234", "a235", "a236", "a237", "a238", "a239", "a2310", "a241", "a242", "a243", "a244", "a25", "a26", "a27", "a31", "a32", "a33", "a34", "a35", "a36", "a37", "a38", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime", "a224"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"糖尿病前期中医健康管理随访监测记录表",annex7TitleName,annex7TitleKey,patientList,annex7List);
        // 7、查询annex7Patient
        List<Annex7Patient> annex7PatientList = annex7PatientService.list(queryWrapper);
        String [] annex7PatientTitleName = {"姓名","id", "患者id", "身高", "体重", "腰围", "臀围", "收缩压",     "舒张压", "空腹血糖mmol/L",     "空腹血糖mg/dL", "健康教育处方-是否了解改善生活方式对改善糖尿病前期有积极意义", "健康教育处方-教授的中医防病技巧是否能熟练运用", "健康教育处方-是否能熟练使用血糖仪自测血糖", "膳食营养-是否注意合理膳食、均衡营养", "膳食营养-是否按照“500111法则”或“手掌法则”执行情况", "膳食营养-推荐中医食疗措施执行情况", "运动管理-有氧运动方式", "运动管理-有氧运动频次", "运动管理-有氧每次运动时长", "运动管理-阻抗运动方式", "运动管理-阻抗运动频次", "运动管理-阻抗每次运动时长", "运动管理-中医运动方式", "运动管理-中医运动频次", "运动管理-中医每次运动时长", "运动管理-其他中医运动管理措施执行情况", "体重管理-体重减轻", "体重管理-主动控制饮食", "体重管理-主动增强运动", "体重管理-中医减重执行情况", "睡眠管理情况-睡眠时间6-8小时", "偏颇体质调理-是否按处方落实调理", "医生开具的其他中医药血糖控制处方-是否按照中医血糖控制处方严格执行", "形-", "神-", "纳-", "眠-", "便-", "下一步指导意见", "是否已退出管理", "退出原因", "第几次录入", "添加时间", "添加用户", "更新时间", "更新用户", "是否是末次录入", "膳食营养-其他中医药管理措施执行情况"};
        String [] annex7PatientTitleKey = {"id", "patientId","a11", "a12", "a13", "a14", "a15",     "a16", "a17",     "a18", "a211", "a212", "a213", "a221", "a222", "a223", "a231", "a232", "a233", "a234", "a235", "a236", "a237", "a238", "a239", "a2310", "a241", "a242", "a243", "a244", "a25", "a26", "a27", "a31", "a32", "a33", "a34", "a35", "a36", "a37", "a38", "number", "createTime", "createUser", "updateTime", "updateUser", "theLastTime", "a224"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"自我健康管理健康信息表",annex7PatientTitleName,annex7PatientTitleKey,patientList,annex7PatientList);
        // 8、查询annex9
        List<Annex9> annex9List = annex9Service.list(queryWrapper);
        String [] annex9TitleName = {"姓名","id", "患者id", "情志调摄", "饮食调养", "起居调摄", "运动保健", "穴位保健", "其他", "身高", "体重", "腰围", "臀围", "血常规-血红蛋白", "血常规-白细胞", "血常规-血小板", "空腹血糖mmol/L", "空腹血糖mg/dL", "糖化血红蛋白", "肝功能-血清谷丙转氨酶", "肝功能-血清谷草转氨酶", "肝功能-结合胆红素", "肝功能-总胆红素", "肝功能-白蛋白", "肾功能-血清肌酐", "肾功能-血钾浓度", "肾功能-血尿素", "肾功能-血钠浓度", "血脂-总胆固醇", "血脂-甘油三酯", "血脂-血清低密度脂蛋白胆固醇", "血脂-血清高密度脂蛋白胆固醇", "第几次录入", "是否是末次录入", "添加时间", "添加用户", "更新时间", "更新用户"};
        String [] annex9TitleKey = {"id", "patientId", "a11", "a12", "a13", "a14", "a15", "a16", "a21", "a22", "a23", "a24", "a311", "a312", "a313", "a321", "a322", "a331", "a341", "a342", "a343", "a344", "a345", "a351", "a352", "a353", "a354", "a361", "a362", "a363", "a364", "number", "theLastTime", "createTime", "createUser", "updateTime", "updateUser"};
        exportAnnex1ToAnnex9ExcelMethod(workbook,"糖尿病患者中医干预随访记录",annex9TitleName,annex9TitleKey,patientList,annex9List);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        workbook.dispose();
        workbook.close();
        out.close();
    }

    // 导出管理组和对照组数据
    private void exportUserAndComparativeExcelMethod(SXSSFWorkbook workbook, String sheetName,String[] titleName,String[] titleKey, List dataList) throws IllegalAccessException, IOException {
        SXSSFSheet sheet = (SXSSFSheet)workbook.createSheet(sheetName);
        Row row = sheet.createRow(0);
        Cell cell;
        for(int i = 0;i < titleName.length; i++){
            cell = row.createCell(i);
            cell.setCellValue(titleName[i]);
        }
        for(int i = 0;i < dataList.size();i++){
            row = sheet.createRow(i+1);
            Object data = dataList.get(i);
            Field[] sonFields = data.getClass().getDeclaredFields();
            Field[] parentFields = data.getClass().getSuperclass().getDeclaredFields();
            Field[] fields = ArrayUtil.addAll(sonFields,parentFields);
            for(int j = 0;j < titleKey.length;j++){
                cell = row.createCell(j);
                for(Field f: fields){
                    if(f.getName().equals(titleKey[j])){
                        f.setAccessible(true);
                        if("createTime".equals(titleKey[j]) || "updateTime".equals(titleKey[j])){
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            cell.setCellValue(f.get(data) == null?"":sdf.format(f.get(data)));
                        }else{
                            cell.setCellValue(f.get(data) == null?"":f.get(data).toString());
                        }
                        if((i+1)%500==0 && (i+1) > 0){
                            sheet.flushRows();
                        }
                        break;
                    }
                }
            }
        }
    }
    // 导出附件1到附件9数据
    private void exportAnnex1ToAnnex9ExcelMethod(SXSSFWorkbook workbook, String sheetName, String[] titleName, String[] titleKey, List<User> patientList, List dataList) throws IllegalAccessException, NoSuchFieldException, IOException {
        SXSSFSheet sheet = (SXSSFSheet)workbook.createSheet(sheetName);
        Row row = sheet.createRow(0);
        Cell cell;
        for(int i = 0;i < titleName.length; i++){
            cell = row.createCell(i);
            cell.setCellValue(titleName[i]);
        }
        int rowCount = 1;
        for(User user: patientList){
            for(int i = 0;i < dataList.size();i++){
                Object data = dataList.get(i);
                Field patientIdField = data.getClass().getDeclaredField("patientId");
                patientIdField.setAccessible(true);
                if(user.getId().equals(patientIdField.get(data))){
                    row = sheet.createRow(rowCount++);
                    cell = row.createCell(0);
                    cell.setCellValue(user.getName());
                    Field[] sonFields = data.getClass().getDeclaredFields();
                    Field[] parentFields = data.getClass().getSuperclass().getDeclaredFields();
                    Field[] fields = ArrayUtil.addAll(sonFields,parentFields);
                    for(int j = 0;j < titleKey.length;j++){
                        cell = row.createCell(j + 1);
                        for(Field f: fields){
                            if(f.getName().equals(titleKey[j])){
                                f.setAccessible(true);
                                if("createTime".equals(titleKey[j]) || "updateTime".equals(titleKey[j])){
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    cell.setCellValue(f.get(data) == null?"":sdf.format(f.get(data)));
                                }else{
                                    cell.setCellValue(f.get(data) == null?"":f.get(data).toString());
                                }
                                if((rowCount - 1) % 500==0 && rowCount > 0){
                                    sheet.flushRows();
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }
    }
}
